from idlelib import tooltip
from math import *
import queue
import sys
import time
import os
from datetime import datetime
import pymssql
import pandas as pd
import numpy as np
import datetime
import math
from folium import plugins
import folium
import os

sys.path.append(r'C:\Users\Wayson\Desktop')
from CarQuery import *
from Arc import *
from ArcMany import *


# 返回两个gps点的实际距离，经纬度与米的换算约为1:111000。/LOU
def DistanceActual(geo1, geo2):
    return pow(pow(float(geo1[0]) - float(geo2[0]), 2) + pow(float(geo1[1]) - float(geo2[1]), 2), 0.5) * 111000


# 如果汽车定位数据跟道路的相差很远或者方向不对，则没有必要取该条arc
def CheckNecessary(arc, car_record):
    if DistanceActual(arc.geometry_list[0], car_record.geo) > 1000:
        if DistanceActual(arc.geometry_list[-1], car_record.geo) > 1000:
            return False
    else:
        return True


def CalCost(arc, car_record):
    cost = float("inf")
    # CheckNecessary函数首先排除与车辆定位点距离相差远、方向不匹配的路段。/LOU
    if CheckNecessary(arc, car_record):
        # 下面if是计算这条道路上所有gps点跟目标点的最短距离小于100m才返回True。我们认为汽车gps精度为20m，放宽到100m
        min_dist = float("inf")
        for geo1 in arc.geometry_list:
            tmp = DistanceActual(geo1, car_record.geo)
            if tmp < min_dist:
                min_dist = tmp
        cost = min_dist
    return cost


# 给定car_geo和car_direction，根据最短距离和方向，计算在所有道路的Cost，返回Cost最小的道路id
def CalMinCostArcID(car_record):
    min_cost = float("inf")
    id = 0
    # arc_objects存储着所有边的Arc对象实例，即所有路段信息。/LOU
    for arc in arc_objects:
        cost = CalCost(arc, car_record)
        if cost < min_cost:
            min_cost = cost
            id = arc.id
    return id


###从SQLsever导入地图数据
conn = pymssql.connect(host='MachineOfWayson',user='',password='',database='DATA')
cur1 = conn.cursor()
cur1.execute("SELECT * FROM [DATA].[dbo].[python路网0] order by 路段ID")
# 如果是插入、删除、更新语句切记要写提交命令con.commit()
Arc_data2 = cur1.fetchall()
cur1.close()
conn.close()
arc_objects = [Arc(line_str) for line_str in Arc_data2]

###将地图的节点改为间距20米的密集节点
conn = pymssql.connect(host='MachineOfWayson',user='',password='',database='DATA')
cur = conn.cursor()
sql = "select [列 0],[列 1],[路段ID] from [DATA].[dbo].[道路密集分布点] order by 路段ID,序号"
cur.execute(sql)
Arc_many = cur.fetchall()
cur.close()
conn.close()
Arc_many_objects = [ArcMany(line_str) for line_str in Arc_many]
Arc_many_id = [Arc_many.id for Arc_many in Arc_many_objects]  # Arc_many_objects的id合集
Arc_many_num = max(Arc_many_id)
Arc_many_count = [Arc_many_id.count(i + 1) for i in range(Arc_many_num)]  # Arc_many_objects每条路的密集点的数量


# 得到id为i的路段的密集点集合
def ArcmanyRecordsDivide(Arcmany_i):
    Arcmany_index_start = sum(Arc_many_count[:Arcmany_i])  # 第i路段的起始索引位置
    Arcmany_index_end = sum(Arc_many_count[:Arcmany_i + 1])  # 第i路段的终止索引位置
    Arcmany_record_list = Arc_many_objects[Arcmany_index_start: Arcmany_index_end]  # 每个路段的点集合
    Arcmany_geo_list = [Arcmany_record.geometry_list2 for Arcmany_record in Arcmany_record_list]  # 每个路段的点的经纬度集合
    return Arcmany_geo_list

def swap_loc(list):
    temp = 0
    for i in range(0,len(list)):
        temp = list[i][1]
        list[i][1] = list[i][0]
        list[i][0] = temp
# arc_objects里的路段经纬度更新为点集合
for i in range(0, len(Arc_data2)):  # 对每条路段循环
    del arc_objects[i].geometry_list[0:4]  # 删除第i条路段原有的经纬度信息
    Arcmany_record_list_i = ArcmanyRecordsDivide(i)  # 提取第i条路段新的密集点经纬度信息
    for j in range(0, len(Arcmany_record_list_i)):  # 对第i条路段新的密集点经纬度信息循环
        arc_objects[i].geometry_list.append(Arcmany_record_list_i[j])  # 在第i条路段经纬度列加上 新的密集点经纬度第j条信息



def SQL_query(var1, var2, var3):
    conn = pymssql.connect(host='MachineOfWayson',user='',password='',database='DATA')
    cur = conn.cursor()
    query1 = "select a.[path],a.[列 4],a.[列 5],a.[列 6],a.[列 7],a.[OD序列号],-1 as score from [output] as a,[output2] as b,[output3] as c where b.[path] = %s and c.[path] = %s and a.[OD序列号]=c.[OD序列号] and b.[OD序列号]=c.[OD序列号] and b.[index_id]<c.[index_id] and b.[index_id]<=a.[index_id] and a.[index_id]<=c.[index_id] and a.[time_stage] = %s order by a.[index_id]"
    cur.execute(query1, (var1, var2, var3))
    data2 = cur.fetchall()
    cur.close()
    conn.close()
    return data2


'''
  select a.[path],a.[列 4],a.[列 5],a.[列 6],a.[列 7],a.[OD序列号],-1 as score
  from [output] as a,[output2] as b,[output3] as c
  where b.[path]=8792 and c.[path]=8746
  and a.[OD序列号]=c.[OD序列号]
  and b.[OD序列号]=c.[OD序列号]
  and b.[index_id]<c.[index_id]
  and b.[index_id]<=a.[index_id]
  and a.[index_id]<=c.[index_id]
  and a.[time_stage] = 3
  order by a.[index_id]


'''


def main():
    temp = 0
    road_list = []
    car_start = [113.31480485, 23.1304351]
    car_end = [113.3101584, 23.13768047]
    time = 3
    car_record1 = CarQuery(car_start)
    car_record2 = CarQuery(car_end)
    path1 = CalMinCostArcID(car_record1)
    # path1 = 9309
    print(path1)
    path2 = CalMinCostArcID(car_record2)
    # path2 = 5919
    print(path2)
    path_query = pd.DataFrame(SQL_query(path1, path2, time))
    print('备选路径集：' + str(path_query))

    b = pd.DataFrame(path_query[5].value_counts())  # 统计每个唯一值出现了多少次，行索引为OD序列号，值为OD序列号对应出现的次数
    b.columns = ['times']  # 修改列名
    print('b改前：' + str(b))
    b = b.loc[b['times'] == b['times'].min()]  # 找出路径最短的一条OD
    print('b改后：' + str(b))
    a = b._stat_axis.values.tolist()  # 提取行索引，即OD序列号
    print('最终选择的OD:' + str(a[0]))  # 选一条OD作为方案
    path = path_query.loc[path_query[5] == a[0]]  # 提取这条OD的数据
    output = path.iloc[:, [0, 1, 2, 3, 4]]  # 提取这条OD的数据
    print('路径集：' + str(output))
    for i in range(0,len(path)):
        road_list.append(path.iloc[i][1:3].values.tolist())
    swap_loc(road_list)#调整坐标顺序
    car_start.reverse()
    car_end.reverse()
    m = folium.Map(car_start,zoom_start=15)  # 中心区域的确定
    route = folium.PolyLine(  # polyline方法为将坐标用线段形式连接起来
        road_list,  # 将坐标点连接起来
        weight=3,  # 线的大小为3
        color='red',  # 线的颜色为橙色
        opacity=0.8  # 线的透明度
    ).add_to(m)  # 将这条线添加到刚才的区域m内
    folium.Marker(car_start,
                  popup='<i>起点<i>',
                  tooltip=tooltip,
                  icon=folium.Icon(color='blue',size='50')).add_to(m)
    folium.Marker(car_end,
                  popup='<i>终点<i>',
                  tooltip=tooltip,
                  icon=folium.Icon(color='red')).add_to(m)
    m.save(os.path.join(r'C:\Users\Wayson\Desktop', 'routeplan.html'))  # 将结果以HTML形式保存到桌面上
    print("结果已导出")
    print(str(a[0]))
    # for i in range(0,len(path)):
    #     temp = road_list[i][1]
    #     road_list[i][1] = road_list[i][0]
    #     road_list[i][0] = temp


if __name__ == '__main__':
    start = time.clock()
    main()
    end = time.clock()
